unit Config.DataBase;

interface

uses
  FireDAC.Comp.Client,
  FireDAC.Stan.Option,
  System.Classes,
  System.IOUtils,
  System.SysUtils,
  IniFiles,
  MVCFramework.ActiveRecord,
  MVCFramework.SQLGenerators.Sqlite,
  MVCFramework.SQLGenerators.PostgreSQL,
  MVCFramework.SQLGenerators.Firebird,
  MVCFramework.SQLGenerators.MSSQL,
  MVCFramework.SQLGenerators.Interbase,
  MVCFramework.SQLGenerators.MySQL;

type
  TRDBMSEngine = (PostgreSQL, Firebird, Interbase, MSSQLServer, MySQL, MariaDB, SQLite);

  type

  TDataBaseDef = class sealed
  private
    class function LoadConfig: TIniFile;
  public
    class function DBMS_ENGINE: string;
    class function CON_DEF_NAME: string;
    class function DATABASE_NAME: string;
    class function SERVER: string;
    class function USER_NAME: string;
    class function PASSWORD: string;
    class function PORT: string;
  end;

procedure CreateFDConn(var fdConn: TFDConnection; RDBMSEngine: TRDBMSEngine = SQLite);
procedure CreateFirebirdPrivateConnDef(AIsPooled: boolean);
procedure CreateInterbasePrivateConnDef(AIsPooled: boolean);
procedure CreateMySQLPrivateConnDef(AIsPooled: boolean);
procedure CreateMSSQLServerPrivateConnDef(AIsPooled: boolean);
procedure CreatePostgresqlPrivateConnDef(AIsPooled: boolean);
procedure CreateSqlitePrivateConnDef(AIsPooled: boolean);
function NewQuery(sSQL: string): TFDQuery;

implementation

function NewQuery(sSQL: string): TFDQuery;
var
  lConn: TFDConnection;
begin
  lConn := ActiveRecordConnectionsRegistry.GetCurrent();

  Result := TFDQuery.Create(lConn); { Query is owned by the connection }
  Result.FetchOptions.Unidirectional := True;
  Result.FetchOptions.Mode := fmOnDemand;
  Result.UpdateOptions.ReadOnly := True;
  Result.Connection := lConn;
  Result.SQL.Text := sSQL;
end;

procedure CreateFDConn(var fdConn: TFDConnection; RDBMSEngine: TRDBMSEngine = SQLite);
begin
  case RDBMSEngine of
    TRDBMSEngine.PostgreSQL:
      begin
        CreatePostgresqlPrivateConnDef(True);
      end;
    TRDBMSEngine.Firebird:
      begin
        CreateFirebirdPrivateConnDef(True);
      end;
    TRDBMSEngine.Interbase:
      begin
        CreateInterbasePrivateConnDef(True);
      end;
    TRDBMSEngine.MySQL:
      begin
        CreateMySQLPrivateConnDef(True);
      end;
    TRDBMSEngine.MariaDB:
      begin
        CreateMySQLPrivateConnDef(True);
      end;
    TRDBMSEngine.SQLite:
      begin
        CreateSqlitePrivateConnDef(True);
      end;
    TRDBMSEngine.MSSQLServer:
      begin
        CreateMSSQLServerPrivateConnDef(True);
      end;
  else
    raise Exception.Create('Unknown RDBMS');
  end;

  fdConn.Params.Clear;
  fdConn.ConnectionDefName := TDataBaseDef.CON_DEF_NAME;
  fdConn.Connected := True;
end;

procedure CreateMySQLPrivateConnDef(AIsPooled: boolean);
var
  LParams: TStringList;
begin
{
  docker run --detach --env MARIADB_USER=example-user --env MARIADB_PASSWORD=my_cool_secret --env MARIADB_ROOT_PASSWORD=root  -p 3306:3306 mariadb:latest
}

  LParams := TStringList.Create;
  try
    LParams.Add('Database=' + TDataBaseDef.DATABASE_NAME);
    LParams.Add('Protocol=TCPIP');
    LParams.Add('Server=' + TDataBaseDef.SERVER);
    LParams.Add('User_Name=' + TDataBaseDef.USER_NAME);
    LParams.Add('Password=' + TDataBaseDef.PASSWORD);
    LParams.Add('TinyIntFormat=Boolean'); { it's the default }
    LParams.Add('CharacterSet=utf8mb4');  //not utf8!!
    if not TDataBaseDef.PORT.IsEmpty then
      LParams.add('Port=' + TDataBaseDef.PORT);
    if AIsPooled then
    begin
      LParams.Add('Pooled=True');
      LParams.Add('POOL_MaximumItems=100');
    end
    else
    begin
      LParams.Add('Pooled=False');
    end;
    FDManager.AddConnectionDef(TDataBaseDef.CON_DEF_NAME, 'MySQL', LParams);
  finally
    LParams.Free;
  end;
end;

procedure CreateMSSQLServerPrivateConnDef(AIsPooled: boolean);
var
  LParams: TStringList;
begin
{
  docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=!SA_password!" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
}

  // [ACTIVERECORDB_SQLSERVER]
  // Database=activerecorddb
  // OSAuthent=Yes
  // Server=DANIELETETI\SQLEXPRESS
  // DriverID=MSSQL
  //

  LParams := TStringList.Create;
  try
    LParams.Add('Database=' + TDataBaseDef.DATABASE_NAME);
//    LParams.Add('OSAuthent=Yes');
    LParams.Add('User_Name=' + TDataBaseDef.USER_NAME);
    LParams.Add('Password=' + TDataBaseDef.PASSWORD);
    if not TDataBaseDef.PORT.IsEmpty then
      LParams.Add('Server=' + TDataBaseDef.SERVER + ', ' + TDataBaseDef.PORT)
    else
      LParams.Add('Server=' + TDataBaseDef.SERVER);
    // LParams.Add('TinyIntFormat=Boolean'); { it's the default }
    if AIsPooled then
    begin
      LParams.Add('Pooled=True');
      LParams.Add('POOL_MaximumItems=100');
    end
    else
    begin
      LParams.Add('Pooled=False');
    end;
    FDManager.AddConnectionDef(TDataBaseDef.CON_DEF_NAME, 'MSSQL', LParams);
  finally
    LParams.Free;
  end;
end;

procedure CreateFirebirdPrivateConnDef(AIsPooled: boolean);
var
  LParams: TStringList;
begin
  LParams := TStringList.Create;
  try
    LParams.Add('Database=' + TPath.GetFullPath('..\data\' + TDataBaseDef.DATABASE_NAME));
    LParams.Add('Protocol=TCPIP');
    LParams.Add('Server=' + TDataBaseDef.SERVER);
    LParams.Add('User_Name=' + TDataBaseDef.USER_NAME);
    LParams.Add('Password=' + TDataBaseDef.PASSWORD);
    LParams.Add('CharacterSet=UTF8');
    if AIsPooled then
    begin
      LParams.Add('Pooled=True');
      LParams.Add('POOL_MaximumItems=100');
    end
    else
    begin
      LParams.Add('Pooled=False');
    end;
    FDManager.AddConnectionDef(TDataBaseDef.CON_DEF_NAME, 'FB', LParams);
  finally
    LParams.Free;
  end;
end;

procedure CreateInterbasePrivateConnDef(AIsPooled: boolean);
var
  LParams: TStringList;
begin
  LParams := TStringList.Create;
  try
    LParams.Add('Database=' + TPath.GetFullPath('..\data\' + TDataBaseDef.DATABASE_NAME));
    LParams.Add('Protocol=TCPIP');
    LParams.Add('Server=' + TDataBaseDef.SERVER);
    LParams.Add('User_Name=' + TDataBaseDef.USER_NAME);
    LParams.Add('Password=' + TDataBaseDef.PASSWORD);
    LParams.Add('CharacterSet=UTF8');
    if AIsPooled then
    begin
      LParams.Add('Pooled=True');
      LParams.Add('POOL_MaximumItems=100');
    end
    else
    begin
      LParams.Add('Pooled=False');
    end;
    FDManager.AddConnectionDef(TDataBaseDef.CON_DEF_NAME, 'IB', LParams);
  finally
    LParams.Free;
  end;
end;

procedure CreatePostgresqlPrivateConnDef(AIsPooled: boolean);
var
  LParams: TStringList;
begin
  LParams := TStringList.Create;
  try
    LParams.Add('Database=' + TDataBaseDef.DATABASE_NAME);
    LParams.Add('Protocol=TCPIP');
    LParams.Add('Server=' + TDataBaseDef.SERVER);
    LParams.Add('User_Name=' + TDataBaseDef.USER_NAME);
    LParams.Add('Password=' + TDataBaseDef.PASSWORD);
    if not TDataBaseDef.PORT.IsEmpty then
      LParams.Add('Port=' + TDataBaseDef.PORT);
    if AIsPooled then
    begin
      LParams.Add('Pooled=True');
      LParams.Add('POOL_MaximumItems=100');
    end
    else
    begin
      LParams.Add('Pooled=False');
    end;
    FDManager.AddConnectionDef(TDataBaseDef.CON_DEF_NAME, 'PG', LParams);
  finally
    LParams.Free;
  end;
end;

procedure CreateSqlitePrivateConnDef(AIsPooled: boolean);
var
  LParams: TStringList;
  lFName: string;
begin
  LParams := TStringList.Create;
  try
    lFName := TPath.GetFullPath('..\data\' + TDataBaseDef.DATABASE_NAME);
    LParams.Add('Database=' + lFName);
    LParams.Add('StringFormat=Unicode');
    if AIsPooled then
    begin
      LParams.Add('Pooled=True');
      LParams.Add('POOL_MaximumItems=100');
    end
    else
    begin
      LParams.Add('Pooled=False');
    end;
    FDManager.AddConnectionDef(TDataBaseDef.CON_DEF_NAME, 'SQLite', LParams);
  finally
    LParams.Free;
  end;
end;


{ TDataBaseDef }

class function TDataBaseDef.DBMS_ENGINE: string;
begin
  Result := TDataBaseDef.LoadConfig.ReadString('Database', 'DBMS_ENGINE', '');
end;

class function TDataBaseDef.CON_DEF_NAME: string;
begin
  Result := TDataBaseDef.LoadConfig.ReadString('Database', 'CON_DEF_NAME', '');
end;

class function TDataBaseDef.DATABASE_NAME: string;
begin
  Result := TDataBaseDef.LoadConfig.ReadString('Database', 'DATABASE_NAME', '');
end;

class function TDataBaseDef.LoadConfig: TIniFile;
var
  IniFilePath: string;
begin

  IniFilePath := ExtractFilePath(ParamStr(0)) + 'env.cfg';
  if not FileExists(IniFilePath) then
  begin
    Result := nil;
    raise Exception.Create('Database Server Setting File not Exists');
  end;
  Result := TIniFile.Create(IniFilePath);
end;

class function TDataBaseDef.PASSWORD: string;
begin
  Result := TDataBaseDef.LoadConfig.ReadString('Database', 'PASSWORD', '');
end;

class function TDataBaseDef.PORT: string;
begin
  Result := TDataBaseDef.LoadConfig.ReadString('Database', 'PORT', '');
end;

class function TDataBaseDef.SERVER: string;
begin
  Result := TDataBaseDef.LoadConfig.ReadString('Database', 'SERVER', '');
end;

class function TDataBaseDef.USER_NAME: string;
begin
  Result := TDataBaseDef.LoadConfig.ReadString('Database', 'USER_NAME', '');
end;

end.
